package com.thd.springboottest.mybatisplus.controller;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.thd.springboottest.mybatisplus.entity.SysUser;
import com.thd.springboottest.mybatisplus.mapper.SysUserMapper;
import com.thd.springboottest.mybatisplus.service.SysUserService;
import com.thd.springboottest.mybatisplus.vo.SysUserVo;
import org.apache.ibatis.annotations.Param;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.ResponseEntity;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import java.util.Date;
import java.util.List;
import java.util.UUID;

/**
 * com.thd.springboottest.mybatisplus.controller.SysUserController
 *
 * @author: wanglei62
 * @DATE: 2019/9/20 16:16
 **/
@Controller
@RequestMapping("/sysUser")
public class SysUserController {
    @Autowired
    private SysUserService sysUserService;
    @Autowired
    private SysUserMapper sysUserMapper;


    /**
     * myBatisPlus 基础方法 save
     * @return
     */
    //url : http://127.0.0.1:8899/thd/sysUser/save
    @RequestMapping("/save")
    @ResponseBody
    public String save(){
        SysUser u = new SysUser();
        u.setUserId("113");
        u.setUserName("test MyBatisPlus Save2");
        u.setUserSex(1);
        u.setUserBirthday(new Date());
        this.sysUserService.save(u);
        return "SUCCESS";
    }


    /**
     * myBatisPlus 基础方法 updateById
     * @return
     */
    //url : http://127.0.0.1:8899/thd/sysUser/updateById
    @RequestMapping("/updateById")
    @ResponseBody
    public String update(){
        SysUser u = new SysUser();
        u.setUserId("11");
        u.setUserName("test mybatisplus update444");
        this.sysUserService.updateById(u);
        return "SUCCESS";
    }

    /**
     * myBatisPlus 基础方法 removeById
     * @return
     */
    //url : http://127.0.0.1:8899/thd/sysUser/removeById
    @RequestMapping("/removeById")
    @ResponseBody
    public String removeById(){
        this.sysUserMapper.deleteById("13");
//        this.sysUserService.removeById("13",true);
        return "SUCCESS";
    }


    /**
     *  调用mapper.xml中SQL的方法
     * @return
     */
    //url : http://127.0.0.1:8899/thd/sysUser/queryList
    @RequestMapping("/queryList")
    @ResponseBody
    public List queryList(){
        SysUser queryCondition = new SysUser();
        queryCondition.setUserId("1");
        return this.sysUserService.queryList(queryCondition);
    }

    /**
     * QueryWrapper的使用
     * @return
     */
    //url : http://127.0.0.1:8899/thd/sysUser/queryByCondition
    @RequestMapping("/queryByCondition")
    @ResponseBody
    public List queryByCondition(){
        QueryWrapper<SysUser> qw = new QueryWrapper<SysUser>();
        qw.eq("user_name","n_8d9640b2-7b8e-46b6-99ec-0cb3cf5c0392");
        return this.sysUserService.queryByCondition(qw);
    }

    //url : http://127.0.0.1:8899/thd/sysUser/queryById/1
    @RequestMapping("/queryById/{id}")
    @ResponseBody
    public SysUser queryById(@PathVariable String id){
        return this.sysUserService.queryById(id);
    }


    /**
     * mybatisPlus单表查询分页
     * @param currentPage
     * @param pageSize
     * @return
     */
    //url : http://127.0.0.1:8899/thd/sysUser/queryByPage/1/5
    @RequestMapping("/queryByPage/{currentPage}/{pageSize}")
    @ResponseBody
    public List queryByPage(@PathVariable int currentPage,@PathVariable int pageSize){
        QueryWrapper<SysUser> qw = new QueryWrapper<SysUser>();
        qw.eq("user_sex",1);
        return this.sysUserService.queryByPage(qw,currentPage,pageSize);
    }

    /**
     * LambdaQueryWrapper的使用
     * @return
     */
    //url : http://127.0.0.1:8899/thd/sysUser/loadByName/7
    @RequestMapping("/loadByName/{name}")
    @ResponseBody
    public ResponseEntity loadByName(@PathVariable String name){
        SysUser u = this.sysUserService.loadByName(name);
        return ResponseEntity.ok("S");
    }

    //url : http://127.0.0.1:8899/thd/sysUser/queryByName/37
    @RequestMapping("/queryByName/{name}")
    @ResponseBody
    public List<SysUser> queryByName(@PathVariable String name){
        return this.sysUserService.queryByName(name);
    }



    //url : http://127.0.0.1:8899/thd/sysUser/testSave
    @RequestMapping("/testSave")
    @ResponseBody
    public SysUser testSave(@PathVariable String name){
        SysUser sysUser = new SysUser();
        sysUser.setUserName(name);
        sysUser.setUserId(UUID.randomUUID().toString());
        this.sysUserService.save(sysUser);
        return sysUser;
    }





    // ==================== mybatisplus querywrapper 高级用法 ======================



    @RequestMapping("/testQueryWrapperSqlSegment")
    @ResponseBody
    //url : http://127.0.0.1:8899/thd/sysUser/testQueryWrapperSqlSegment
    public List<SysUser> testQueryWrapperSqlSegment(){

        // mapper.xml中直接使用queryWrapper生成的where SQL语句，查看SysUserMapper.xml中的sqlSegment方法
        QueryWrapper<SysUser> qw = new QueryWrapper<SysUser>();
        qw.eq("user_name","zhangsan");
        qw.or( q -> {
            q.eq("user_sex","1");
            q.eq("user_sex","2");
        });
        qw.or( q -> {
            q.eq("user_sex","3");
            q.eq("user_sex","4");
        });
        System.out.println(qw.getSqlSegment());

        // 可以在QueryWrapper生成的SQL后面自定义一些SQL语句
        qw.last("order by user_name asc limit 0 ,2");
        return this.sysUserService.testSqlSegment(qw);
    }

    /**
     * 使用wrapper拼sql
     * @return
     */
    @RequestMapping("/testQueryWrapperAndXml")
    @ResponseBody
    //url : http://127.0.0.1:8899/thd/sysUser/testQueryWrapperAndXml
    public SysUser testQueryWrapperAndXml(){

        this.sysUserService.testQueryWrapperAndXml();
        return null;
    }


    @RequestMapping("/testQueryWrapperLast")
    @ResponseBody
    //url : http://127.0.0.1:8899/thd/sysUser/testQueryWrapperLast
    public List<SysUser> testQueryWrapperLast(){

        // 执行后观察控制台  ======> Execute SQL： 后面的SQL，将带有order by user_name asc
        QueryWrapper<SysUser> qw = new QueryWrapper<SysUser>();
        // 可以在QueryWrapper生成的SQL后面自定义一些SQL语句
        qw.last("order by user_name asc limit 0 ,2");
        return this.sysUserService.list(qw);
    }


    @RequestMapping("/testQueryWrapperSelect")
    @ResponseBody
    //url : http://127.0.0.1:8899/thd/sysUser/testQueryWrapperSelect
    public List<SysUser> testQueryWrapperSelect(){

        // 可以指定字段进行查询
        // 执行后观察控制台  ======> Execute SQL：SELECT user_name,user_sex FROM sys_user
        QueryWrapper<SysUser> qw = new QueryWrapper<SysUser>();
        // 可以在QueryWrapper生成的SQL后面自定义一些SQL语句
        qw.select("uuid() as user_id","user_name","user_sex");

        System.out.println(qw.getSqlSelect());
        return this.sysUserService.list(qw);
    }


    @RequestMapping("/testQueryWrapperApply")
    @ResponseBody
    //url : http://127.0.0.1:8899/thd/sysUser/testQueryWrapperApply
    public List<SysUser> testQueryWrapperApply(){

        // 自定义SQL 通过apply方法添加查询条件
        // 执行后观察控制台  ======> Execute SQL：SELECT user_name,user_sex FROM sys_user
        QueryWrapper<SysUser> qw = new QueryWrapper<SysUser>();
        qw.eq("user_sex",5);
        // 可以在QueryWrapper生成的SQL后面自定义一些SQL语句
        qw.apply(" user_name like {0} ","%zhangsan%");
        qw.apply(" user_name like {0} ","%lisi%");
        qw.apply(" user_name like {0} ","%wangwu%");
        qw.apply(" user_name like {0} ","%zhaoliu%");
        System.out.println(qw.getSqlSegment());
        return this.sysUserService.list(qw);
    }


    @RequestMapping("/testQueryWrapperAndOr")
    @ResponseBody
    //url : http://127.0.0.1:8899/thd/sysUser/testQueryWrapperAndOr
    public List<SysUser> testQueryWrapperAndOr(){

        // and or 嵌套

        QueryWrapper<SysUser> qw = new QueryWrapper<SysUser>();
        qw.eq("user_name","zhangsan");
        qw.or( q -> {
            q.eq("user_sex","1");
            q.eq("user_sex","2");
        });
        qw.or( q -> {
            q.eq("user_sex","3");
            q.eq("user_sex","4");
        });
        return this.sysUserService.list(qw);
    }


    @RequestMapping("/testQueryWrapperExist")
    @ResponseBody
    //url : http://127.0.0.1:8899/thd/sysUser/testQueryWrapperExist
    public List<SysUser> testQueryWrapperExist(){

        // exist 自定义sql

        QueryWrapper<SysUser> qw = new QueryWrapper<SysUser>();

        qw.exists(" select user_id from sys_user");
        System.out.println(qw.getSqlSegment());
        return this.sysUserService.list(qw);
    }


    /**
     * mapper.xml中定义的sql 用IPage进行分页
     * @param sysUserVo
     * @return
     */
    @RequestMapping("/pageJoinQuery")
    @ResponseBody
    //url : http://127.0.0.1:8899/thd/sysUser/pageJoinQuery?currentPage=1&pageSize=3&userName=zhangsan&orgName=腾讯
    public IPage<SysUserVo> pageJoinQuery(SysUserVo sysUserVo){
        IPage<SysUserVo> page =new Page<>(sysUserVo.getCurrentPage(),sysUserVo.getPageSize());
        IPage<SysUserVo> r = this.sysUserService.pageJoinQuery(page,sysUserVo);
        return r;
    }


    @RequestMapping("/testParamAnnotation")
    @ResponseBody
    //url : http://127.0.0.1:8899/thd/sysUser/testParamAnnotation
    public List<SysUser> testParamAnnotation(){

        SysUser u = new SysUser();
        u.setUserName("zhangsan");
        List<SysUser> r = this.sysUserMapper.testParamAnnotation(u);
        return r;
    }







}
